package com.isyscore.os.metadata.database;

import com.isyscore.os.core.exception.DataFactoryException;
import com.isyscore.os.core.exception.ErrorCode;
import com.isyscore.os.core.sqlcore.MSSQLBuilder;
import com.isyscore.os.metadata.enums.DataSourceTypeEnum;
import com.isyscore.os.metadata.model.dto.DataSourceDTO;
import com.isyscore.os.metadata.model.vo.ResultVO;
import org.apache.commons.lang3.StringUtils;

import java.util.Arrays;
import java.util.List;
import java.util.Map;
import java.util.Objects;
import java.util.regex.Pattern;
import java.util.stream.Collectors;

/**
 * @Description :
 * @Author: qkc
 * @Date: 2021/9/29 11:35
 */
public class SqlServerDatabase extends AbstractDatabase {

    {
        dbType = DataSourceTypeEnum.SQLSERVER;
        //时间类型
        DATE_TYPE.add("DATE");
        DATE_TYPE.add("TIMESTAMP");
        DATE_TYPE.add("DATETIME2");
        DATE_TYPE.add("TIME");

        //字段类型
        COLUMN_TYPE.put("varchar", "varchar(255)");
        COLUMN_TYPE.put("smallint", "smallint");
        COLUMN_TYPE.put("int", "int");
        COLUMN_TYPE.put("decimal", "decimal(18)");
        COLUMN_TYPE.put("bigint", "bigint");
        COLUMN_TYPE.put("nvarchar", "nvarchar(255)");
        COLUMN_TYPE.put("nchar", "nchar");
        COLUMN_TYPE.put("char", "char");
        COLUMN_TYPE.put("ntext", "ntext");
        COLUMN_TYPE.put("text", "text");
        COLUMN_TYPE.put("float", "float(53)");
        COLUMN_TYPE.put("datetime", "datetime");
        COLUMN_TYPE.put("date", "date");

        builder = new MSSQLBuilder();
    }

    private final Pattern orderByPattern = Pattern.compile(".*\\s+order\\s+by\\s+.*",
            Pattern.MULTILINE | Pattern.CASE_INSENSITIVE);

    private final String RENAME_TABLE = "EXEC sp_rename '%s', '%s'";

    private final String SELECT_TABLE_COL =
            "SELECT" +
                    " obj.name AS tableName," +
                    " col.name AS columnName," +
                    " col.name AS oldName," +
                    " 'tableComment' AS tableComment," +
                    " ISNULL( ep.[value], '' ) AS columnComment," +
                    " t.name AS dataType," +
                    " t.name + '(' + CONVERT ( VARCHAR ( 10 ), col.length ) + ')' AS columnType " +
                    "FROM" +
                    " dbo.syscolumns col" +
                    " LEFT JOIN dbo.systypes t ON col.xtype = t.xusertype" +
                    " INNER JOIN dbo.sysobjects obj ON col.id = obj.id " +
                    " AND obj.xtype = 'U' " +
                    " AND obj.status >= 0" +
                    " LEFT JOIN dbo.syscomments comm ON col.cdefault = comm.id" +
                    " LEFT JOIN sys.extended_properties ep ON col.id = ep.major_id " +
                    " AND col.colid = ep.minor_id " +
                    " AND ep.name = 'MS_Description'" +
                    " LEFT JOIN sys.extended_properties epTwo ON obj.id = epTwo.major_id " +
                    " AND epTwo.minor_id = 0 " +
                    " AND epTwo.name = 'MS_Description' " +
                    " INNER JOIN sys.schemas sch ON obj.uid = sch.schema_id" +
                    " AND sch.name = '%s' " +
                    "WHERE" +
                    " obj.name = '%s' --表名 " +
                    "ORDER BY" +
                    " col.colorder";

    private final String SELECT_TABLE =
            "SELECT" +
                    " objs.Name AS tableName " +
                    "FROM" +
                    " sys.SysObjects objs," +
                    " sys.schemas sch " +
                    "WHERE" +
                    " objs.XType= 'U' AND sch.schema_id = objs.uid AND sch.name = '%s'";

    private final String SELECT_TABLE_COLUMN_LINK =
            "SELECT" +
                    " obj.name AS tableName," +
                    " col.name AS columnName," +
                    " col.name AS oldName," +
                    " ISNULL( ep.[value], '' ) AS columnComment," +
                    " t.name AS dataType," +
                    " t.name + '(' + CONVERT ( VARCHAR ( 10 ), col.length ) + ')' AS columnType ," +
                    " CASE" +
                    "   WHEN EXISTS (" +
                    "   SELECT" +
                    "     1 " +
                    "   FROM" +
                    "     dbo.sysindexes si" +
                    "     INNER JOIN dbo.sysindexkeys sik ON si.id = sik.id " +
                    "     AND si.indid = sik.indid" +
                    "     INNER JOIN dbo.syscolumns sc ON sc.id = sik.id " +
                    "     AND sc.colid = sik.colid" +
                    "     INNER JOIN dbo.sysobjects so ON so.name = si.name " +
                    "     AND so.xtype = 'PK' " +
                    "   WHERE" +
                    "     sc.id = col.id " +
                    "     AND sc.colid = col.colid " +
                    "     ) THEN" +
                    "     'PK' ELSE '' " +
                    "   END AS columnKey  " +
                    "FROM" +
                    " dbo.syscolumns col" +
                    " LEFT JOIN dbo.systypes t ON col.xtype = t.xusertype" +
                    " INNER JOIN dbo.sysobjects obj ON col.id = obj.id " +
                    " AND obj.xtype = 'U' " +
                    " AND obj.status >= 0" +
                    " LEFT JOIN dbo.syscomments comm ON col.cdefault = comm.id" +
                    " LEFT JOIN sys.extended_properties ep ON col.id = ep.major_id " +
                    " AND col.colid = ep.minor_id " +
                    " AND ep.name = 'MS_Description'" +
                    " LEFT JOIN sys.extended_properties epTwo ON obj.id = epTwo.major_id " +
                    " AND epTwo.minor_id = 0 " +
                    " AND epTwo.name = 'MS_Description' " +
                    " INNER JOIN sys.schemas sch ON obj.uid = sch.schema_id" +
                    " AND sch.name = '%s'" +
                    "WHERE" +
                    " obj.name IN ( SELECT obj.name from dbo.syscolumns col" +
                    " INNER JOIN dbo.sysobjects obj ON col.id = obj.id " +
                    " AND obj.xtype = 'U' " +
                    " AND obj.status >= 0" +
                    " INNER JOIN sys.schemas sch ON obj.uid = sch.schema_id" +
                    " AND sch.name = '%s'" +
                    " WHERE col.name IN (%s)) ";

    private final String QUOTE = "\"";

    @Override
    public String escapeTbName(String tableName, String dbName) {
        //databaseName.scheme -> [databaseName].[schema].[tableName]
        if (!dbName.contains(".")) {
            throw new DataFactoryException(ErrorCode.INPUT_PARAM_ILLEGAL);
        }
        return Arrays.stream(dbName.split("\\.")).map(s -> "[" + s + "]").collect(Collectors.joining(".")) + ".[" + tableName + "]";
    }

    @Override
    public String escapeColName(String originColName) {
        return QUOTE + originColName + QUOTE;
    }

    @Override
    public String escapeColAliasName(String originAliasName) {
        return QUOTE + originAliasName + QUOTE;
    }

    @Override
    public String withOutEscapeColName(String escapeColName) {
        return escapeColName.replaceAll(QUOTE,"");
    }

    @Override
    public String withOutEscapeTableName(String escapeTableName) {
        return escapeTableName.replaceAll(QUOTE,"");
    }

    @Override
    public String getParseStr2DateEl(String dateVal) {
        return "CONVERT(datetime," + dateVal + ",120)";
    }

    @Override
    public String getParseDate2StrEl(String dateVal) {
        return null;
    }

    @Override
    public String getParseStr2IntEl(String dateVal) {
        return "convert(bigint,"+dateVal+")";
    }

    /**
     * 解析SQLSERVER的数据库和模式拼接字符串 databaseName.schema -> schema
     *
     * @param dbName
     * @return
     */
    @Override
    public String escapeDbName(String dbName) {
        if (!dbName.contains(".")) {
            //默认返回guest模式
            return null;
        }
        return dbName.split("\\.")[1];
    }

    @Override
    public String jdbcUrl(String ip, int port, String dbName, String basicType, String basicValue) {
        String url = DataSourceTypeEnum.SQLSERVER.getUrl();
        //解析SQLSERVER的数据库和模式拼接字符串 databaseName.schema -> databaseName
        if (StringUtils.isBlank(dbName)) {
            dbName = "";
        } else if (dbName.contains(".")) {
            dbName = dbName.split("\\.")[0];
        }
        return String.format(url, ip, port, dbName);
    }

    @Override
    public String tableStructSql(String tableName, String dbName) {
        return String.format(SELECT_TABLE_COL, dbName, tableName);
    }

    @Override
    public String tableListSql(String dbName) {
        return String.format(SELECT_TABLE, dbName);
    }

    @Override
    public String linkTableSql(String columns, String dbName) {
        return String.format(SELECT_TABLE_COLUMN_LINK, dbName, dbName, columns);
    }

    @Override
    public String renameTableSql(String oldTableName, String newTableName) {
        return String.format(RENAME_TABLE, oldTableName, newTableName);
    }

    @Override
    public void wrapTableData(ResultVO resultVO) {
        resultVO.setHead(resultVO.getHead().stream()
                .filter(col -> !Objects.equals(col.getColumnName(), OracleDatabase.ROW_ID))
                .collect(Collectors.toList()));

        List<Map<String, Object>> body = resultVO.getContent();
        for (Map<String, Object> map : body) {
            map.remove(OracleDatabase.ROW_ID);
        }
    }

//    @Override
//    public String pageSql(String sql, int offset, int limit) {
//        //sqlserver的offset-fetch方式分页必须要有order by语句，没有的话需要人工添加一个伪order by语句
//        //目前mp官方也没解决该问题，见https://github.com/baomidou/mybatis-plus/issues/3969
//        String pageSql = sql;
//        if (!orderByPattern.matcher(sql).find()) {
//            pageSql = sql + " ORDER BY CURRENT_TIMESTAMP";
//        }
//        pageSql = pageSql + " OFFSET " + offset + " ROWS FETCH NEXT " + limit + " ROWS ONLY";
//        return pageSql;
//    }

    @Override
    public String pageSql(String sql, int offset, int limit) {
        String pageSql = String.format("SELECT * FROM (SELECT ROW_NUMBER() OVER (ORDER BY CURRENT_TIMESTAMP) AS RowNumber, t.* FROM (%s) AS t) AS t2 WHERE RowNumber BETWEEN %s AND %s", sql,offset,offset+limit);
        return pageSql;
    }

    @Override
    public String dropTableSql(String tableName) {
        String DROP_TABLE = "DROP TABLE %s";
        return String.format(DROP_TABLE, tableName);
    }

    @Override
    public String getQuote() {
        return QUOTE;
    }

    public List<String> schemas(DataSourceDTO dataSourceDTO) {
        String SCHEMAS = "SELECT name FROM sys.schemas WHERE schema_id NOT IN (3, 4) AND schema_id < 16384";
        ResultVO resultVO = execSqlAndGet(dataSourceDTO, SCHEMAS);
        return resultVO.getContent().parallelStream().map(map -> (String) map.get("name")).collect(Collectors.toList());
    }

    @Override
    public String copyStructSql(String fromTable, String newTable) {
        String sql = "select * into %s from %s where 1=2";
        return String.format(sql,newTable,fromTable);
    }

}
